/*==============================================================*/
/* DBMS name:      Sybase AS Enterprise 15.0.2                  */
/* Created on:     2011-10-30 12:45:55                          */
/*==============================================================*/


if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('Album'))
            where name = 'FK_ALBUM_REFERENCE_DORMITOR' and type = 'RI')
   alter table Album
      drop constraint FK_ALBUM_REFERENCE_DORMITOR
go

if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('Diarycomment'))
            where name = 'FK_DIARYCOM_REFERENCE_DIARY' and type = 'RI')
   alter table Diarycomment
      drop constraint FK_DIARYCOM_REFERENCE_DIARY
go

if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('Diarycomment'))
            where name = 'FK_DIARYCOM_REFERENCE_DORMITOR' and type = 'RI')
   alter table Diarycomment
      drop constraint FK_DIARYCOM_REFERENCE_DORMITOR
go

if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('Dormnewscomment'))
            where name = 'FK_DORMNEWS_REFERENCE_DORMNEWS' and type = 'RI')
   alter table Dormnewscomment
      drop constraint FK_DORMNEWS_REFERENCE_DORMNEWS
go

if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('Dormnewscomment'))
            where name = 'FK_DNEWSCOM_REFERENCE_DORMITOR' and type = 'RI')
   alter table Dormnewscomment
      drop constraint FK_DNEWSCOM_REFERENCE_DORMITOR
go

if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('Message'))
            where name = 'FK_MESSAGE_REFERENCE_DORMITOR' and type = 'RI')
   alter table Message
      drop constraint FK_MESSAGE_REFERENCE_DORMITOR
go

if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('Vistors'))
            where name = 'FK_VISTORS_REFERENCE_DORMITOR' and type = 'RI')
   alter table Vistors
      drop constraint FK_VISTORS_REFERENCE_DORMITOR
go

if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('diary'))
            where name = 'FK_DIARY_REFERENCE_STUBASIC' and type = 'RI')
   alter table diary
      drop constraint FK_DIARY_REFERENCE_STUBASIC
go

if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('diary'))
            where name = 'FK_DIARY_REFERENCE_DORMITOR' and type = 'RI')
   alter table diary
      drop constraint FK_DIARY_REFERENCE_DORMITOR
go

if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('dormnews'))
            where name = 'FK_DORMNEWS_REFERENCE_STUBASIC' and type = 'RI')
   alter table dormnews
      drop constraint FK_DORMNEWS_REFERENCE_STUBASIC
go

if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('dormnews'))
            where name = 'FK_DORMNEWS_REFERENCE_DORMITOR' and type = 'RI')
   alter table dormnews
      drop constraint FK_DORMNEWS_REFERENCE_DORMITOR
go

if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('friends'))
            where name = 'FK_FRIENDS_REFERENCE_DORMITOR' and type = 'RI')
   alter table friends
      drop constraint FK_FRIENDS_REFERENCE_DORMITOR
go

if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('picture'))
            where name = 'FK_PICTURE_REFERENCE_ALBUM' and type = 'RI')
   alter table picture
      drop constraint FK_PICTURE_REFERENCE_ALBUM
go

if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('picturecomment'))
            where name = 'FK_PICTUREC_REFERENCE_DORMITOR' and type = 'RI')
   alter table picturecomment
      drop constraint FK_PICTUREC_REFERENCE_DORMITOR
go

if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('picturecomment'))
            where name = 'FK_PICTUREC_REFERENCE_PICTURE' and type = 'RI')
   alter table picturecomment
      drop constraint FK_PICTUREC_REFERENCE_PICTURE
go

if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('stubasic'))
            where name = 'FK_STUBASIC_REFERENCE_DORMITOR' and type = 'RI')
   alter table stubasic
      drop constraint FK_STUBASIC_REFERENCE_DORMITOR
go

if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('stumore'))
            where name = 'FK_STUMORE_REFERENCE_STUBASIC' and type = 'RI')
   alter table stumore
      drop constraint FK_STUMORE_REFERENCE_STUBASIC
go

if exists (select 1
            from  sysobjects
            where id = object_id('Album')
            and   type = 'U')
   drop table Album
go

if exists (select 1
            from  sysobjects
            where id = object_id('Diarycomment')
            and   type = 'U')
   drop table Diarycomment
go

if exists (select 1
            from  sysobjects
            where id = object_id('Dormnewscomment')
            and   type = 'U')
   drop table Dormnewscomment
go

if exists (select 1
            from  sysobjects
            where id = object_id('Message')
            and   type = 'U')
   drop table Message
go

if exists (select 1
            from  sysobjects
            where id = object_id('Vistors')
            and   type = 'U')
   drop table Vistors
go

if exists (select 1
            from  sysobjects
            where id = object_id('diary')
            and   type = 'U')
   drop table diary
go

if exists (select 1
            from  sysobjects
            where id = object_id('dormitory')
            and   type = 'U')
   drop table dormitory
go

if exists (select 1
            from  sysobjects
            where id = object_id('dormnews')
            and   type = 'U')
   drop table dormnews
go

if exists (select 1
            from  sysobjects
            where id = object_id('friends')
            and   type = 'U')
   drop table friends
go

if exists (select 1
            from  sysobjects
            where id = object_id('picture')
            and   type = 'U')
   drop table picture
go

if exists (select 1
            from  sysobjects
            where id = object_id('picturecomment')
            and   type = 'U')
   drop table picturecomment
go

if exists (select 1
            from  sysobjects
            where id = object_id('stubasic')
            and   type = 'U')
   drop table stubasic
go

if exists (select 1
            from  sysobjects
            where id = object_id('stumore')
            and   type = 'U')
   drop table stumore
go

/*==============================================================*/
/* Table: Album                                                 */
/*==============================================================*/
create table Album (
   alid                 integer                        identity,
   dormid               integer                        null,
   albumname            varchar(50)                    null,
   surface              varchar(100)                   null,
   description          varchar(500)                   null,
   isdeleted            tinyint                        null,
   constraint PK_ALBUM primary key (alid)
)
go

/*==============================================================*/
/* Table: Diarycomment                                          */
/*==============================================================*/
create table Diarycomment (
   daid                 integer                        not null,
   dctime               datetime                       null,
   content              varchar(500)                   null,
   isdeleted            tinyint                        null,
   commentor            integer                        null
)
go

/*==============================================================*/
/* Table: Dormnewscomment                                       */
/*==============================================================*/
create table Dormnewscomment (
   dnid                 integer                        not null,
   content              varchar(500)                   null,
   pctime               datetime                       null,
   isdeleted            tinyint                        null,
   commentor            integer                        null
)
go

/*==============================================================*/
/* Table: Message                                               */
/*==============================================================*/
create table Message (
   mid                  integer                        identity,
   leaver               varchar(20)                    null,
   todorm               integer                        null,
   mtime                datetime                       null,
   content              varchar(1000)                  null,
   mtype                tinyint                        null,
   specific             varchar(20)                    null,
   isdeleted            tinyint                        null,
   constraint PK_MESSAGE primary key (mid)
)
go

/*==============================================================*/
/* Table: Vistors                                               */
/*==============================================================*/
create table Vistors (
   vid                  integer                        identity,
   dormid               integer                        null,
   vtime                datetime                       null,
   vistorid             integer                        null,
   smallimage           varchar(50)                    null,
   constraint PK_VISTORS primary key (vid)
)
go

/*==============================================================*/
/* Table: diary                                                 */
/*==============================================================*/
create table diary (
   daid                 integer                        identity,
   dormid               integer                        null,
   title                varchar(100)                   null,
   dtime                datetime                       null,
   content              text                           null,
   author               varchar(20)                    null,
   isdeleted            tinyint                        null,
   constraint PK_DIARY primary key (daid)
)
go

/*==============================================================*/
/* Table: dormitory                                             */
/*==============================================================*/
create table dormitory (
   dormid               integer                        not null,
   dormname             varchar(50)                    not null,
   smallimage           varchar(50)                    not null,
   bigimage             varchar(50)                    not null,
   motto                varchar(100)                   not null,
   title                varchar(50)                    not null,
   description          text                           not null,
   visitrecord          integer                        not null,
   credits              integer                        not null,
   constraint PK_DORMITORY primary key (dormid)
)
go

/*==============================================================*/
/* Table: dormnews                                              */
/*==============================================================*/
create table dormnews (
   dnid                 integer                        identity,
   dormid               integer                        null,
   news                 varchar(500)                   null,
   publishor            varchar(20)                    null,
   ptime                datetime                       null,
   isdeleted            tinyint                        null,
   constraint PK_DORMNEWS primary key (dnid)
)
go

/*==============================================================*/
/* Table: friends                                               */
/*==============================================================*/
create table friends (
   fid                  integer                        identity,
   dormid               integer                        null,
   friendid             integer                        null,
   status               tinyint                        default 0 null,
   constraint PK_FRIENDS primary key (fid)
)
go

/*==============================================================*/
/* Table: picture                                               */
/*==============================================================*/
create table picture (
   pid                  integer                        identity,
   alid                 integer                        null,
   picname              varchar(50)                    null,
   picttime             datetime                       null,
   description          varchar(500)                   null,
   isdeleted            tinyint                        null,
   constraint PK_PICTURE primary key (pid)
)
go

/*==============================================================*/
/* Table: picturecomment                                        */
/*==============================================================*/
create table picturecomment (
   pid                  integer                        not null,
   content              varchar(500)                   null,
   pictime              datetime                       null,
   commentor            integer                        null,
   isdeleted            tinyint                        null
)
go

/*==============================================================*/
/* Table: stubasic                                              */
/*==============================================================*/
create table stubasic (
   username             varchar(50)                    not null,
   password             varchar(50)                    not null,
   email                varchar(50)                    not null,
   studentid            varchar(20)                    not null,
   dormid               integer                        not null,
   grade                tinyint                        default 0 null,
   validation           tinyint                        default 0 null,
   constraint PK_STUBASIC primary key (studentid)
)
go

/*==============================================================*/
/* Table: stumore                                               */
/*==============================================================*/
create table stumore (
   studentid            varchar(20)                    not null,
   clazz                varchar(20)                    not null,
   sex                  tinyint                        not null,
   birthday             date                           null,
   nickname             varchar(50)                    null,
   safequestion         varchar(50)                    null,
   safesanswer          varchar(50)                    null,
   emailchecked         tinyint                        null,
   smallimage           varchar(100)                   null,
   bigimage             varchar(100)                   null,
   selfintroduce        text                           null,
   leader               tinyint                        null,
   position             varchar(50)                    null,
   college              varchar(100)                   null
)
go

alter table Album
   add constraint FK_ALBUM_REFERENCE_DORMITOR foreign key (dormid)
      references dormitory (dormid)
go

alter table Diarycomment
   add constraint FK_DIARYCOM_REFERENCE_DIARY foreign key (daid)
      references diary (daid)
go

alter table Diarycomment
   add constraint FK_DIARYCOM_REFERENCE_DORMITOR foreign key (commentor)
      references dormitory (dormid)
go

alter table Dormnewscomment
   add constraint FK_DORMNEWS_REFERENCE_DORMNEWS foreign key (dnid)
      references dormnews (dnid)
go

alter table Dormnewscomment
   add constraint FK_DNEWSCOM_REFERENCE_DORMITOR foreign key (commentor)
      references dormitory (dormid)
go

alter table Message
   add constraint FK_MESSAGE_REFERENCE_DORMITOR foreign key (todorm)
      references dormitory (dormid)
go

alter table Vistors
   add constraint FK_VISTORS_REFERENCE_DORMITOR foreign key (dormid)
      references dormitory (dormid)
go

alter table diary
   add constraint FK_DIARY_REFERENCE_STUBASIC foreign key (author)
      references stubasic (studentid)
go

alter table diary
   add constraint FK_DIARY_REFERENCE_DORMITOR foreign key (dormid)
      references dormitory (dormid)
go

alter table dormnews
   add constraint FK_DORMNEWS_REFERENCE_STUBASIC foreign key (publishor)
      references stubasic (studentid)
go

alter table dormnews
   add constraint FK_DORMNEWS_REFERENCE_DORMITOR foreign key (dormid)
      references dormitory (dormid)
go

alter table friends
   add constraint FK_FRIENDS_REFERENCE_DORMITOR foreign key (dormid)
      references dormitory (dormid)
go

alter table picture
   add constraint FK_PICTURE_REFERENCE_ALBUM foreign key (alid)
      references Album (alid)
go

alter table picturecomment
   add constraint FK_PICTUREC_REFERENCE_DORMITOR foreign key (commentor)
      references dormitory (dormid)
go

alter table picturecomment
   add constraint FK_PICTUREC_REFERENCE_PICTURE foreign key (pid)
      references picture (pid)
go

alter table stubasic
   add constraint FK_STUBASIC_REFERENCE_DORMITOR foreign key (dormid)
      references dormitory (dormid)
go

alter table stumore
   add constraint FK_STUMORE_REFERENCE_STUBASIC foreign key (studentid)
      references stubasic (studentid)
go

